#!/usr/bin/env python
# -*- coding: utf-8 -*-

import sys
import os
import _mysql, _mysql_exceptions
from datetime import date, timedelta
from sqlquery import *
import glob
reload(sys)
sys.setdefaultencoding('utf-8')


# 店铺统计库
# dbwww58com_shop_stats


def connect_to_dianpu(db='dbwww58com_shopemc'):
    host = 'shopemc-s.db.58dns.org'
    port = 58885
    user = 'shopuser_fenxi'
    passwd = 'mYmq6U.^~V'

    dbh = _mysql.connect(host, user, passwd, db, port)
    return dbh


def connect_to_shangpin(db='dbwww58com_emcproduct_0'):
    host = 'ecmproduct0-s.db.58dns.org'
    port = 58885
    user = 'emcproduct_hprro'
    passwd = 'OqX1jJ3Qjy'

    # dbh = _mysql.connect(host, user, passwd, db, port, init_command='set names utf8')
    dbh = _mysql.connect(host, user, passwd, db, port)
    return dbh


def connect_to_dingdan(db='dbwww58com_emcorder'):
    host = 'emcorder-s.db.58dns.org'
    port = 58885
    user = 'emcorder_hprro'
    passwd = 'Yb%LEfmLO5'

    dbh = _mysql.connect(host, user, passwd, db, port)
    return dbh


def connect_to_50(db='dbwww58com_shop_stats'):
    host = '10.5.20.16'
    port = 58885
    user = 'ec_stat'
    passwd = 'L#c8UM5RGh5C'

    dbh = _mysql.connect(host, user, passwd, db, port)
    return dbh


def query(dbh, q):
    try:
        dbh.query(q)
    except _mysql.MySQLError, e:
        print e
        print q
        return None

    result = dbh.store_result()
    rows = result.fetch_row(0)
    return rows


def insertinto(dbh, tablename, field, value):
    if len(field) != len(value):
        print 'the len of field is not equal len of value:'
        print '*' * 60
        print field
        print value
        print '*' * 60
        return False

    q = "INSERT INTO %s (%s) VALUES (%s);" % (tablename, ','.join(field), ','.join(map(lambda x: "'" + str(x) + "'", value)))
    #print q
    try:
        #print q
        dbh.query(q)
    except _mysql_exceptions.IntegrityError, e:
        #print q
        print e
        return False

    return True


def update(dbh, tablename, field, value, restriction):
    # sometimes the value is just a int or float
    if isinstance(value, int) or isinstance(value, float):
        pass
    elif len(field) != len(value):
        print 'the len of field is not equal len of value:'
        print '*' * 60
        print field
        print value
        print '*' * 60
        return False

    if isinstance(value, int) or isinstance(value, float):
        updatefield = field + '=' + '"' + str(value) + '"'
    else:
        updatefield = ','.join([f + '=' + '"' + str(v) + '"' for f, v in zip(field, value)])

    q = 'UPDATE %s SET %s WHERE %s;' %(tablename, updatefield ,restriction)
    # print q

    try:
        dbh.query(q)
    except _mysql_exceptions.IntegrityError, e:
        print e
        return False

    return True


def get_a_string(dbh, q):
    try:
        r = query(dbh, q)[0][0]
    except IndexError, e:
        #print 'get_a_string Error: ' + str(e)
        #print q
        return None
    return r


def get_a_number(dbh, q):
    try:
        r = query(dbh, q)[0][0]
    except IndexError, e:
        print 'get_a_number Error: ' + str(e)
        #print q
        # raise IndexError
        r = 0

    if r is None:
        return 0

    try:
        n = int(r)
    except:
        n = float(r)

    return n


def get_a_value(dbh, q):
    r = query(dbh, q)[0][0]
    try:
        n = int(r)
    except:
        try:
            n = float(r)
        except:
            n = r

    return n


def str_day_start_and_end(y, m, d, h=None):
    start = date(y, m, d)
    end = start + timedelta(1)
    start = start.isoformat()
    end = end.isoformat()

    if h is not None:
        end = start + " %02d:59:00" % (h)
        start += " %02d:00:00" % (h)

    return start, end


def filter_total_good_bad_comment(comment):
    # 1 for good, 2 middle, 3 for bad
    ncomment = len(comment)
    ngood = len(filter(lambda x: x == '1', comment))
    nbad = len(filter(lambda x: x == '3', comment))

    return ncomment, ngood, nbad


def get_comment_stats(q):
    dbh = connect_to_dianpu()
    rows = query(dbh, q)

    desc = ['comment', 'goodcomment', 'badcomment', 'goodcommentratio', 'badcommentratio']

    if rows is None or len(rows) == 0:
        return desc, [0, 0, 0, 0, 0]

    comment = [r[0] for r in rows]
    ncomment, ngood, nbad = filter_total_good_bad_comment(comment)
    rgood = float(ngood) / ncomment
    rbad = float(nbad) / ncomment

    return desc, [ncomment, ngood, nbad, rgood, rbad]


def get_product_info(dtstart, dtend, p):
    desc = []
    querys = []

    desc.append('bookingproduct')
    q = query_num_of_boking_product(dtstart, dtend, p)
    querys.append(q)

    # a order may have many products
    desc.append('bookingorder')
    q = query_num_of_boking_order(dtstart, dtend, p)
    querys.append(q)

    desc.append('bookingorderfee')
    q = query_num_of_boking_order_fee(dtstart, dtend, p)
    querys.append(q)

    desc.append('paidorder')
    q = query_num_of_paid_order(dtstart, dtend, p)
    querys.append(q)

    desc.append('productselled')
    q = query_num_of_paid_product(dtstart, dtend, p)
    querys.append(q)

    desc.append('paidfee')
    q = query_num_of_order_total_fee(dtstart, dtend, p)
    querys.append(q)

    desc.append('paiduser')
    q = query_num_of_user_paid(dtstart, dtend, p)
    querys.append(q)

    # # TODO: xxxx
    # desc.append('newpaiduser')
    # q = query_num_of_new_user_paid(dtstart, dtend, p)
    # querys.append(q)

    desc.append('refundorderbeforeticket')
    q = query_order_back_before_ticket_checking(dtstart, dtend, p)
    querys.append(q)

    desc.append('refundproductbeforeticket')
    q = query_product_back_before_ticket_checking(dtstart, dtend, p)
    querys.append(q)

    desc.append('refundfeebeforeticket')
    q = query_fee_back_before_ticket_checking(dtstart, dtend, p)
    querys.append(q)

    desc.append('refunduserbeforeticket')
    q = query_user_back_before_ticket_checking(dtstart, dtend, p)
    querys.append(q)

    desc.append('refunduserafterticket')
    q = query_user_back_after_ticket_checking(dtstart, dtend, p)
    querys.append(q)

    dbh = connect_to_dingdan()
    nums = [get_a_number(dbh, q) for q in querys]

    return desc, nums

def writeTextToFile(path,fileName,mode,content):
    try:
        if not os.path.exists(path):
            os.makedirs(path)
        #print '------------',path    
        f=open(path+os.path.sep+fileName,mode)
        f.write(content)
        f.close()
        return True
    except Exception,e:
        print e
        return False
    finally:
        pass
    
def load_data(conn,path,table_name,split):    
    load_sql="LOAD DATA LOCAL INFILE '%s' INTO TABLE %s  FIELDS TERMINATED BY '%s' LINES TERMINATED BY '\n'"%(path,table_name,split)
    #print load_sql
    query(conn,load_sql)
    

if __name__ == '__main__':
    print connect_to_dingdan()
    print connect_to_shangpin()
    print connect_to_dingdan()
    print str_day_start_and_end(2010, 3, 13)
    print str_day_start_and_end(2010, 3, 13, h=2)
